package ch.fhnw.kosmonauten.server.dal;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ch.fhnw.kosmonauten.model.Category;

/**
 * Diese Klasse den Zugriff auf die Kategorien.
 * 
 * @author Fabian Siegrist
 * @author Oliver Freiermuth
 * @author Mikhail Onoufrienko
 * @author Risto Milani
 * @version $Revision: 187 $
 *          File name: $HeadURL: http://iching-kosmonauten.googlecode.com/svn/I-Ching/src/ch/fhnw/kosmonauten/server/dal/CategoryAccessLayer.java $
 *          Last modified:$Date: 2013-05-26 21:02:05 +0000 (Sun, 26 May 2013) $
 *          Last modified by: $Author: molotok.mihon@gmail.com $
 */

// class definition
public class CategoryAccessLayer {

	// attributes
	private Statement s;
	private ResultSet rs;
	private Connection con;
	private Category cat;

	// methods
	/**
	 * @return
	 * @throws SQLException 
	 */
	public Category[] getCategories() throws SQLException {

		ArrayList<Category> response = new ArrayList<Category>();

		try {
			con = DriverManager.getConnection(ConnectionString.url,
					ConnectionString.name, ConnectionString.passw);
			s = con.createStatement();
			rs = s.executeQuery("select * from category;");
			while (rs.next()) {
				response.add(new Category(rs.getString(1), rs.getString(2)));
			}
			return response.toArray(new Category[0]);
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return null;
		}finally {
			rs.close();
			s.close();
			con.close();
		}
	}
	
	public boolean setCategory (Category category) throws SQLException{
		
		this.cat = category;
		
		try{
			con = DriverManager.getConnection(ConnectionString.url,
					ConnectionString.name, ConnectionString.passw);
			s = con.createStatement();
			s.executeUpdate("INSERT into category (Categoryname) values ('" + category.getCategoryName() + "');");
			return true;
		}
		catch (Exception e){
			System.out.println(e.getMessage());
			return false;
		}
		finally{
			s.close();
			con.close();
		}
	}
	
	public boolean changeCategory(Category category) throws SQLException{
		
		this.cat = category;
		
		try{
			con = DriverManager.getConnection(ConnectionString.url,
					ConnectionString.name, ConnectionString.passw);
			s = con.createStatement();
			s.executeUpdate("UPDATE category SET Categoryname = '" + cat.getCategoryName() +  "' WHERE CategoryID = " + cat.getCategoryID() + ";");
			return true;
		}
		catch (Exception e){
			System.out.println(e.getMessage());
			return false;
		}
		finally{
			s.close();
			con.close();
		}
	}
}
